package com.jyxd.web.util;

import java.util.HashMap;
import java.util.Map;

public class SqlUtil {

    /**
     * 替换SQL语句中的占位符
     *
     * @param sql SQL语句，包含占位符 #{key}
     * @param params 参数映射，键为占位符名称，值为替换内容
     * @return 替换后的SQL语句
     */
    public static String replacePlaceholders(String sql, Map<String, Object> params) {
        if (sql == null || params == null) {
            return sql;
        }

        for (Map.Entry<String, Object> entry : params.entrySet()) {
            String key = entry.getKey();
            Object value = entry.getValue();
            String placeholder = "#{" + key + "}";
            String replacement;

            if (value instanceof String) {
                replacement = "'" + value.toString().replace("'", "''") + "'";
            } else if (value == null) {
                replacement = "NULL";
            } else {
                replacement = value.toString();
            }

            sql = sql.replace(placeholder, replacement);
        }

        return sql;
    }

    public static void main(String[] args) {
        String sql = "WITH PatientScores AS (\n" +
                "    SELECT ps.visit_id, ps.score, ps.score_time,\n" +
                "           ROW_NUMBER() OVER (PARTITION BY ps.visit_id ORDER BY ps.score DESC) as rn\n" +
                "    FROM p_patient_score ps\n" +
                "    WHERE ps.type = 'apache2'\n" +
                "      AND ps.status != -1\n" +
                "      AND ps.score_time BETWEEN DATE(#{startDate}) AND DATE_ADD(DATE(#{endDate}), INTERVAL 1 DAY)\n" +
                "),\n" +
                "RankedScores AS (\n" +
                "    SELECT ps.visit_id, ps.score, ps.score_time\n" +
                "    FROM PatientScores ps\n" +
                "    WHERE ps.rn = 1\n" +
                "),\n" +
                "Greater15VisitIds AS (\n" +
                "    SELECT rs.visit_id\n" +
                "    FROM RankedScores rs\n" +
                "    WHERE rs.score > 15\n" +
                "),\n" +
                "ScoreCounts AS (\n" +
                "    SELECT \n" +
                "        COALESCE(SUM(CASE WHEN rs.score > 15 THEN 1 ELSE 0 END), 0) AS greater15Number,\n" +
                "        COALESCE(SUM(CASE WHEN rs.score <= 15 THEN 1 ELSE 0 END), 0) AS lessOrEqual15Number\n" +
                "    FROM RankedScores rs\n" +
                "),\n" +
                "Result AS (\n" +
                "    SELECT p.name AS '姓名', \n" +
                "           CASE \n" +
                "               WHEN p.sex = 0 THEN '未知'\n" +
                "               WHEN p.sex = 1 THEN '男'\n" +
                "               ELSE '女'\n" +
                "           END AS '性别'\n" +
                "    FROM Greater15VisitIds g15v\n" +
                "    JOIN p_patient p ON g15v.patient_id = p.id\n" +
                ")\n" +
                "SELECT '大于15' AS project_name, GROUP_CONCAT(visit_id SEPARATOR ',') AS project_value\n" +
                "FROM Greater15VisitIds\n" +
                "UNION ALL\n" +
                "SELECT '姓名' AS project_name, GROUP_CONCAT(姓名 SEPARATOR ',') AS project_value\n" +
                "FROM Result\n" +
                "UNION ALL\n" +
                "SELECT '性别' AS project_name, GROUP_CONCAT(性别 SEPARATOR ',') AS project_value\n" +
                "FROM Result;\n";

        Map<String, Object> params = new HashMap<>();
        params.put("startDate", "2023-10-01");
        params.put("endDate", "2023-10-31");

        String replacedSql = replacePlaceholders(sql, params);
        System.out.println(replacedSql);
    }
}
